#encoding: utf-8
load 'rds_api.rb'
load 'tutem_utils.rb'

@rds = @rds_tt

# Usage: import-followup.rb xlsx
# 将第三方系统生成的随访数据xlsx导入followup数据库中

# template_id: 3 图腾之友长期用户合格用户及试用期满用户首次发放前随访 TUPAL-001, 来源为腾讯文档的数据收集表导出结果
def get_template_fields template_id = 3
  return [
    'nickname','department','submit_at','user_type','uid',
    'usage','gender','age','goods','channel',
    'purposes','therapy_before','related_docs',
    'start_time','daily_serve','other_treatment','early_intolerance','intolerance_description',
    'tooktime','review','explain','latest_docs','advise','comment','canShare','signature'
  ] if template_id == 3
  return [] 
end

#从第三方随访表中读取数据
def get_followup_recs_from xlsx, fields

    puts "reading #{xlsx}..."

    recs = []

    s = SimpleSpreadsheet::Workbook.read xlsx
    s.selected_sheet = s.sheets.first
    line_idx = 0

    s.first_row.upto(s.last_row) do |line|
        
        print '.'

        rec = {}
        line_idx += 1
        next if line_idx == 1

        #根据field读取到
        idx = 0
        fields.each do |f|
          idx += 1
          v = s.cell(line,idx)
          v = v.to_s if v.nil?
          rec.store f, v
        end

        recs += [rec]

    end

    puts "done"

    return recs
end

def import_data_from fn, code

    puts "import data from #{fn} with code #{code}..."

    tempalte_id = 3
    template_id = 3 if code.include? 'TUPAL'
    stores = '图腾生命医学平台'
    recs = get_followup_recs_from fn, (get_template_fields template_id)

    usqls = []
    isqls = []
    wmsgs = [] #准备发给企业微信的信息

    recs.each do |r|

        uid = r['uid'].to_i
        print "#{uid == 0  ? 'x' : '.'}"
        next if uid == 0

        now = Time.now.to_i
        name = r['nickname'] + "的图腾之友长期用户支持计划随访问卷 #{code}"
        status = 1
        submit_time = Time.now.to_i
        submit_at = ''
        if r['submit_at'].class == DateTime
            submit_time = r['submit_at'].to_time.to_i
            submit_at = Time.at(submit_time).strftime('%Y-%m-%d')
        else
            submit_time = Time.parse(r['submit_at']).to_i #String
            submit_at = Time.at(submit_time).strftime('%Y-%m-%d')
        end
        content = "您已完成图腾之友长期用户支持计划的每月随访数据填写，感谢信任和支持！"
        answers = r.to_json

        #可以用field数组来做copy&paste
        channel = r['channel']
        purposes = r['purposes']
        usage = r['usage']
        goods = r['goods']
        age = r['age']
        gender = r['gender']
        therapy_before = r['therapy_before']
        related_docs = r['related_docs']
        start_time = r['start_time']
        daily_serve = r['daily_serve']
        tooktime = r['tooktime']
        other_treatment = r['other_treatment']
        early_intolerance = r['early_intolerance']
        intolerance_description = r['intolerance_description']
        review = r['review']
        explain = r['explain']
        latest_docs = r['latest_docs']
        advise = r['advise']
        comment = r['comment']
        nickname = r['nickname']
        canShare = r['canShare']
        signature = r['signature']

        related_docs = related_docs.gsub('<u>','').gsub('</u>','').gsub('<html>','').gsub('</html>','') #去除多余的html tag
        latest_docs = latest_docs.gsub('<u>','').gsub('</u>','').gsub('<html>','').gsub('</html>','') #去除多余的html tag

        summary = "本人（#{nickname}）经#{channel}，为#{usage}购买#{goods}，针对#{purposes}使用。\n使用者#{age}岁#{gender}性，使用前情况为#{therapy_before}，相关诊断文件网址：#{related_docs}。\n从#{start_time}开始使用，每天#{daily_serve}，到目前共使用#{tooktime}个月，期间配合措施：#{other_treatment}。\n服用早期#{early_intolerance=='否' ? '未出现不耐受现象' : '出现不耐受现象，具体表现'}#{intolerance_description}。\n本人认为作用方面#{review}，具体原因是#{explain}，最新诊断文件网址：#{latest_docs}。\n使用心得：#{advise}\n建议意见：#{comment}。\n以上所内容及资料，本人#{canShare == '是' ? '确认' : '未确认'}可提供公众参考及诊疗研究之用，签名确认地址：#{signature}"

        wmsg = "用户##{uid}于#{submit_at}通过提交了#{code}随访数据，内容摘要如下：\n#{summary}\n更多详情请随访管理中查看"
        wmsgs +=  [wmsg]

        can_share = 0
        can_share if canShare == '是'
        latest_update = "#{Time.now} 导入#{fn}更新"
        line_feed = "\n"

        usql = "update tutem_mall.eb_follow_up set
            template_id = #{template_id}, uid = #{uid}, submit_time = #{submit_time},
            content = '#{@rds.escape content}', answers = '#{@rds.escape answers}', summary = '#{@rds.escape summary}',
            comment = '#{latest_update}', history = concat(history, concat('#{@rds.escape line_feed}','#{latest_update}'))
            where name like '%#{code}%' and uid = #{uid}, can_share = #{can_share}
            "

        history = "#{r['submit_at']} 用户提交随访数据"
        isql = "insert into tutem_mall.eb_follow_up(
            template_id,uid,name,status,create_time,submit_time,content,answers,summary,comment, history, can_share) select
                #{template_id}, #{uid}, '#{@rds.escape name}', 1, #{now}, #{submit_time},
                '#{@rds.escape content}', '#{@rds.escape answers}', '#{@rds.escape summary}', '#{@rds.escape latest_update}', '#{@rds.escape history}',
                #{can_share}
            from dual
            where not exists ( select id from tutem_mall.eb_follow_up where name like '%#{code}%' and uid = #{uid});"

        usqls += [usql]
        isqls += [isql]

    end

    commitTrans usqls
    commitTrans isqls
    wmsgs.each do |msg|
        send_message_to_bot 'WWBOT_XT', msg, ['@all']
    end

    puts "done"

end

#更新现有记录的canShare
def fix_existed_recs_for_canShare

    puts "update can_share according to answers..."

    sql = "select * from tutem_mall.eb_follow_up where status = 1"
    usqls = []
    (queryRds(sql)).each do |q|

        fid = q['id']
        uid = q['uid']
        name = q['name']
        recorded_canShare = q['can_share']

        answers = JSON.parse q['answers'] 
        canShare = answers['canShare'].to_s
        can_share = canShare.empty? ? 0 : 1
        can_share = 0 if canShare.include? '不'
        can_share = 0 if canShare.include? '否'

        if can_share != recorded_canShare
            usql = "update tutem_mall.eb_follow_up set can_share = #{can_share} where id = #{fid}; #fid=#{fid} uid=#{uid}"
            usqls += [usql]
        end

    end

    commitTrans usqls
    puts "done"

end
#fix_existed_recs_for_canShare

#修正无bill_id的记录，同时更新积分发放的comment，这个comment未来也会是推送给用户的公众号模板消息
#同时也更新对应bill_id中的link_id
def fix_existed_recs_for_bill

    puts "fix extened fields..."

    sql = "select * from tutem_mall.eb_follow_up where status = 1 and bill_id = 0"
    usqls = []
    (queryRds(sql)).each do |q|

        fid = q['id']
        uid = q['uid']
        name = q['name']

        code = ''
        code += name.split(' ').last.to_s if name.include?(' ')#"XXXX S1" "XXXX S2"
        code += name.split('(').last.to_s if name.include?(')')#"XXXX（2021H2）
        code += name.split('（').last.to_s if name.include?('）')#"XXXX（2021H2）
        code = code.gsub(')','').gsub('）','').gsub(' ','')
        title = ''
        title += name.split(' ').first.to_s if name.include?(' ')#"XXXX S1" "XXXX S2"
        title += name.split('(').first.to_s if name.include?(')')#"XXXX（2021H2）
        title += name.split('（').first.to_s if name.include?('）')#"XXXX（2021H2）
        code = 'MANUAL-' + code if !code.include? 'TUPAL'
        full_title = "#{title} #{code}"

        using_tupal = false
        using_tupal = true if name.include? 'TUPAL-001'

        submit_time = q['submit_time']
        like_tupal = " or ( link_id like '%TUPAL-001%' or title like '%TUPAL-001%') "
        inq = "select count(*) as count, id, number, from_unixtime(add_time) as reward_time from tutem_mall.eb_user_bill where uid = #{uid} 
            and ( 
                (  add_time < #{submit_time} + 10 and add_time > #{submit_time} - 10 )
                    #{ using_tupal ? like_tupal : ''}
                )
            and category = 'integral' and type = 'system_add'"
        count = 0
        (queryRds(inq)).each do |i|
            count = i['count']
            if count == 1  #当没有记录的时候，count(*)会导致返回值为零，其它为nil; 有多个记录的情况也不处理
                bill_id = i['id']
                usql = "update tutem_mall.eb_user_bill set link_id = '#{code}' where id = #{bill_id}"
                usqls += [usql]
                reward_at = Time.at(i['reward_time']).strftime('%Y-%m-%d %H:%M:%S')
                bonus = i['number'].to_f.round(0)
                comment = "#{reward_at} 系统已发放#{bonus}积分"
                history = "\n#{reward_at} 系统已发放#{bonus}积分"
                usql = "update tutem_mall.eb_follow_up set name = '#{@rds.escape full_title}', bill_id = #{bill_id}, comment = '#{@rds.escape comment}', history = concat(history, '#{@rds.escape history}') where id = #{fid}"
                usqls += [usql]
            end
            break
        end

        char = count.to_s
        char = "[#{fid.to_s}u#{uid.to_s}]" if count == 0
        #print char

    end

    commitTrans usqls
    puts "done"

end
#fix_existed_recs_for_bill

code = ARGV[0].to_s #expecting TUPAL-001
begin
    fn = "图腾之友随访登记表_#{code}.xlsx"
    import_data_from  fn, code
rescue => e
    puts "something wrong"
    puts e.message
end
